﻿/*
* Slickflow 软件遵循自有项目开源协议，也可联系作者获取企业版商业授权和技术支持；
* 除此之外的使用则视为不正当使用，请您务必避免由此带来的一切商业版权纠纷和损失。
* 
The Slickflow Open License (SfPL 1.0)
Copyright (C) 2014  .NET Workflow Engine Library

1. Slickflow software must be legally used, and should not be used in violation of law, 
   morality and other acts that endanger social interests;
2. Non-transferable, non-transferable and indivisible authorization of this software;
3. The source code can be modified to apply Slickflow components in their own projects 
   or products, but Slickflow source code can not be separately encapsulated for sale or 
   distributed to third-party users;
4. The intellectual property rights of Slickflow software shall be protected by law, and
   no documents such as technical data shall be made public or sold.
5. The enterprise, ultimate and universe version can be provided with commercial license, 
   technical support and upgrade service.
*/

using System;
using System.ComponentModel;
using System.Data;
using System.Dynamic;
using System.Reflection;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;
using System.Data.SqlClient;
using DapperExtensions.Sql;

namespace Slickflow.Data
{
    /// <summary>
    /// Repository基类
    /// </summary>
    public class Repository : IRepository
    {
        public Repository()
        {

        }

        /// <summary>
        /// 根据主键ID获取记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryId"></param>
        /// <returns></returns>
        public T GetById<T>(dynamic primaryId) where T : class
        {
            IDbConnection conn = SessionFactory.CreateConnection();
            try
            {
                return conn.Get<T>(primaryId as object);
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryId"></param>
        /// <returns></returns>
        public T GetById<T>(IDbConnection conn, dynamic primaryId, IDbTransaction trans) where T : class
        {
            return conn.Get<T>(primaryId as object, trans);
        }

        /// <summary>
        /// 根据字段列名称获取实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="colName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public T GetDefaultByName<T>(string colName, string value) where T : class
        {
            var dataList = GetByName<T>(colName, value).ToList<T>();

            if (dataList.Count() > 0)
                return dataList.FirstOrDefault<T>();
            else
                return null;
        }

        /// <summary>
        /// 获取表名称
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public string GetTableName<T>() where T : class
        {
            System.Attribute attr = System.Attribute.GetCustomAttributes(typeof(T))[0];
            var tableName = (attr as dynamic).TableName;
            return tableName;
        }

        /// <summary>
        /// 根据字段列名称获取实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="colName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public IList<T> GetByName<T>(string colName, string value) where T : class
        {
            var tblName = GetTableName<T>();
            var sql = string.Format("SELECT * FROM {0} WHERE {1}=@colValue", tblName, colName);

            try
            {
                using (IDbConnection conn = SessionFactory.CreateConnection())
                {
                    IList<T> dataList = SqlMapper.Query<T>(conn, sql, new { colValue = value }).ToList();
                    return dataList;
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 根据多个Id获取多个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids"></param>
        /// <returns></returns>
        public IEnumerable<T> GetByIds<T>(IList<dynamic> ids) where T : class
        {
            var tblName = GetTableName<T>();
            var idsin = string.Join(",", ids.ToArray<dynamic>());
            var sql = string.Format("SELECT * FROM dbo.{0} WHERE Id in (@ids)", tblName);

            IDbConnection conn = SessionFactory.CreateConnection();
            try
            {
                IEnumerable<T> dataList = SqlMapper.Query<T>(conn, sql, new { ids = idsin });
                return dataList;
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 获取全部数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public IEnumerable<T> GetAll<T>() where T : class
        {
            IDbConnection conn = SessionFactory.CreateConnection();
            try
            {
                IEnumerable<T> dataList = conn.GetList<T>();
                return dataList;
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 查询匹配的一条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public T GetFirst<T>(string sql, dynamic param = null, bool buffered = true) where T : class
        {
            T entity = null;
            IDbConnection conn = SessionFactory.CreateConnection();
            try
            {
                var list = SqlMapper.Query<T>(conn, sql, param as object, null, buffered).ToList();
                if (list != null && list.Count() > 0)
                {
                    entity = list[0];
                }
                return entity;
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public T GetFirst<T>(IDbConnection conn, string sql, dynamic param = null, IDbTransaction trans = null,
            bool buffered = true) where T : class
        {
            T entity = null;
            var list = SqlMapper.Query<T>(conn, sql, param as object, trans, buffered).ToList();
            if (list != null && list.Count() > 0)
            {
                entity = list[0];
            }
            return entity;

        }

        /// <summary>
        /// 根据条件筛选出数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(string sql, dynamic param = null, bool buffered = true) where T : class
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return SqlMapper.Query<T>(conn, sql, param as object, null, buffered);
            }
        }


        /// <summary>
        /// 根据条件筛选出数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(IDbConnection conn, string sql, dynamic param = null, IDbTransaction trans = null, bool buffered = true) where T : class
        {
            return SqlMapper.Query<T>(conn, sql, param as object, trans, buffered);
        }

        /// <summary>
        /// 根据条件筛选数据集合
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public IEnumerable<dynamic> Query(IDbConnection conn, string sql, dynamic param = null, IDbTransaction trans = null, bool buffered = true)
        {
            return SqlMapper.Query(conn, sql, param as object, trans, buffered);
        }

        /// <summary>
        /// 根据表达式筛选
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sql"></param>
        /// <param name="map"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="splitOn"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(IDbConnection conn, string sql, Func<TFirst, TSecond, TReturn> map,
            dynamic param = null, IDbTransaction transaction = null, bool buffered = true,
            string splitOn = "Id", int? commandTimeout = null)
        {
            return SqlMapper.Query(conn, sql, map, param as object, transaction, buffered, splitOn);
        }

        /// <summary>
        /// 查询列表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <param name="sort"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public IEnumerable<T> GetList<T>(IDbConnection conn, IPredicate predicate = null, IList<ISort> sort = null,
            bool buffered = false) where T : class
        {
            return conn.GetList<T>(predicate, sort, null, null, buffered);
        }

        /// <summary>
        /// 分页方法调用示例：
        /// 1. 单一条件
        //  using (SqlConnection cn = new SqlConnection(_connectionString))
        //  {
        //    cn.Open();
        //
        //    //排序字段
        //    var sortList = new List<DapperExtensions.ISort>();
        //    sortList.Add(new DapperExtensions.Sort { PropertyName = "ID", Ascending = false });
        //
        //    var predicate = Predicates.Field<Person>(f => f.Active, Operator.Eq, true);
        //    List<Person> list = cn.GetPaged<Person>(cn, query.PageIndex, query.PageSize, 
        //            predicate, sortList, false).ToList();
        //
        //    cn.Close();
        //  }
        //
        //  2. 组合条件
        //  using (SqlConnection cn = new SqlConnection(_connectionString))
        //  {
        //    cn.Open();
        //
        //    //排序字段
        //    var sortList = new List<DapperExtensions.ISort>();
        //    sortList.Add(new DapperExtensions.Sort { PropertyName = "ID", Ascending = false });
        //
        //    var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
        //    pg.Predicates.Add(Predicates.Field<Person>(f => f.Active, Operator.Eq, true));
        //    pg.Predicates.Add(Predicates.Field<Person>(f => f.LastName, Operator.Like, "Br%"));
        //
        //    List<Person> list = cn.GetPaged<Person>(cn, query.PageIndex, query.PageSize, 
        //            pg, sortList, false).ToList();
        //
        //    cn.Close();
        //  }
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="allRowsCount">总记录数</param>
        /// <param name="predicate">条件</param>
        /// <param name="sort">排序</param>
        /// <param name="buffered">缓存</param>
        /// <returns></returns>
        public IEnumerable<T> GetPaged<T>(IDbConnection conn, int pageIndex, int pageSize, object predicate,
            IList<ISort> sort = null, bool buffered = false) where T : class
        {
            return conn.GetPage<T>(predicate, sort, pageIndex, pageSize, null, null, buffered);
        }

        /// <summary>
        /// 分页查询（存储过程）
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="conn">连接</param>
        /// <param name="pager">分页对象</param>
        /// <param name="buffered">缓存</param>
        /// <returns></returns>
        public IEnumerable<T> GetPaged<T>(IDbConnection conn, Pager pager, bool buffered = false) where T : class
        {
            var tblName = string.IsNullOrEmpty(pager.TableName) ? GetTableName<T>() : pager.TableName;
            var keyFieldName = string.IsNullOrEmpty(pager.KeyFieldName) ? "ID" : pager.KeyFieldName;

            var p = new DynamicParameters();
            p.Add("@pageIndex", pager.PageIndex);
            p.Add("@pageSize", pager.PageSize);
            p.Add("@tblName", tblName);
            p.Add("@fldName", keyFieldName);
            p.Add("@isDesc", pager.IsDesc);
            p.Add("@strWhere", pager.StrWhere);
            p.Add("@fldOrder", pager.FieldOrder);

            return conn.Query<T>("pr_sys_QueryPaged", p, null, buffered, null, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 统计记录总数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate">条件</param>
        /// <param name="buffered">缓存</param>
        /// <returns></returns>
        public int Count<T>(IDbConnection conn, IPredicate predicate, bool buffered = false) where T : class
        {
            return conn.Count<T>(predicate);
        }

        /// <summary>
        /// 统计查询语句记录总数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public int Count<T>(IDbConnection conn, string sql, bool buffered = false) where T : class
        {
            var cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                int count = (int)(ExecuteScalar(conn, cmd) ?? 0);
                return count;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        /// <summary>
        /// 带参数的SQL的Count求和
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Count(string sql, DynamicParameters parameters = null)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<int>(sql, parameters).Single<int>();
            }
        }

        /// <summary>
        /// 获取多实体集合
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public SqlMapper.GridReader GetMultiple(string sql, dynamic param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType? commandType = null)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 执行sql操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Execute(IDbConnection conn, string sql, dynamic param = null, IDbTransaction transaction = null)
        {
            return conn.Execute(sql, param as object, transaction);
        }

        /// <summary>
        /// 执行command操作
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public int ExecuteCommand(IDbCommand cmd)
        {
            return cmd.ExecuteNonQuery();
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int ExecuteProc(string procName, DynamicParameters param = null)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Execute(procName, param, null, null, CommandType.StoredProcedure);
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int ExecuteProc(IDbConnection conn, string procName, DynamicParameters param = null)
        {
            return conn.Execute(procName, param, null, null, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 存储过程执行方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<T> ExecProcQuery<T>(IDbConnection conn, string procName, DynamicParameters param)
            where T : class
        {
            IList<T> list = conn.Query<T>(procName, param, null, false, null, CommandType.StoredProcedure).ToList<T>();
            return list;
        }

        /// <summary>
        /// 存储过程执行方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<T> ExecProcQuery<T>(string procName, DynamicParameters param)
            where T : class
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                IList<T> list = conn.Query<T>(procName, param, null, false, null, CommandType.StoredProcedure).ToList<T>();
                return list;
            }
        }

        /// <summary>
        /// 执行SQL语句，返回查询结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public object ExecuteScalar(IDbConnection conn, string sql, bool buffered = false)
        {
            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            return ExecuteScalar(conn, cmd);
        }

        /// <summary>
        /// 执行SQL语句，并返回数值
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public object ExecuteScalar(IDbConnection conn, IDbCommand cmd)
        {
            try
            {
                bool wasClosed = conn.State == ConnectionState.Closed;
                if (wasClosed) conn.Open();

                return cmd.ExecuteScalar();
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        /// <summary>
        /// 插入实体
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="entity">实体</param>
        /// <returns>主键自增值</returns>
        public dynamic Insert<T>(T entity) where T : class
        {
            var newId = 0;
            var session = SessionFactory.CreateSession();

            session.BeginTrans();
            try
            {
                newId = Insert<T>(session.Connection, entity, session.Transaction);
                session.Commit();
            }
            catch (System.Exception)
            {
                session.Rollback();
                throw;
            }
            finally
            {
                session.Dispose();
            }
            return newId;
        }


        /// <summary>
        /// 插入单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public dynamic Insert<T>(IDbConnection conn, T entity, IDbTransaction transaction = null) where T : class
        {
            dynamic result = conn.Insert<T>(entity, transaction);
            return result;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体</param>
        /// <returns>是否成功</returns>
        public bool Update<T>(T entity) where T : class
        {
            var session = SessionFactory.CreateSession();

            session.BeginTrans();
            try
            {
                var isOk = Update<T>(session.Connection, entity, session.Transaction);
                session.Commit();

                return isOk;
            }
            catch (System.Exception)
            {
                session.Rollback();
                throw;
            }
            finally
            {
                session.Dispose();
            }
        }

        /// <summary>
        /// 更新单条记录
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体</param>
        /// <returns>是否成功</returns>
        public bool Update<T>(IDbConnection conn, T entity, IDbTransaction transaction = null) where T : class
        {
            bool isOk = conn.Update<T>(entity, transaction);
            return isOk;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete<T>(dynamic id) where T : class
        {
            var session = SessionFactory.CreateSession();

            session.BeginTrans();
            try
            {
                var isOk = Delete<T>(session.Connection, id, session.Transaction);
                session.Commit();
                return isOk;
            }
            catch (System.Exception)
            {
                session.Rollback();
                throw;
            }
            finally
            {
                session.Dispose();
            }
        }

        /// <summary>
        /// 删除单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryId"></param>
        /// <returns></returns>
        public bool Delete<T>(IDbConnection conn, dynamic primaryId, IDbTransaction transaction = null) where T : class
        {
            var entity = GetById<T>(primaryId);
            var obj = entity as T;
            bool isOk = conn.Delete<T>(obj, transaction);
            return isOk;
        }

        /// <summary>
        /// 删除单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool Delete<T>(IDbConnection conn, IPredicate predicate, IDbTransaction transaction = null) where T : class
        {
            return conn.Delete<T>(predicate, transaction);
        }

        /// <summary>
        /// 批量插入功能
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityList"></param>
        public void InsertBatch<T>(IDbConnection conn, IEnumerable<T> entityList, IDbTransaction transaction = null) where T : class
        {
			var tblName = string.Format("dbo.{0}", typeof(T).Name);
            var tran = (SqlTransaction)transaction;
            using (var bulkCopy = new SqlBulkCopy(conn as SqlConnection, SqlBulkCopyOptions.TableLock, tran))
            {
                bulkCopy.BatchSize = entityList.Count();
                bulkCopy.DestinationTableName = tblName;
                var table = new DataTable();
                DapperExtensions.Sql.ISqlGenerator sqlGenerator = new SqlGeneratorImpl(new DapperExtensionsConfiguration());
                var classMap = sqlGenerator.Configuration.GetMap<T>();
                var props = classMap.Properties.Where(x=>x.Ignored == false).ToArray();
                foreach (var propertyInfo in props)
                {
                    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyInfo.PropertyType) ?? propertyInfo.PropertyInfo.PropertyType);
                }
                var values = new object[props.Count()];
                foreach (var itemm in entityList)
                {
                    for (var i = 0; i < values.Length; i++)
                    {
                        values[i] = props[i].PropertyInfo.GetValue(itemm, null);
                    }
                    table.Rows.Add(values);
                }
                bulkCopy.WriteToServer(table);
            }
        }

        /// <summary>
        /// 批量更新（）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityList"></param>
        /// <returns></returns>
        public bool UpdateBatch<T>(IDbConnection conn, IEnumerable<T> entityList, IDbTransaction transaction = null) where T : class
        {
            bool isOk = false;
            foreach (var item in entityList)
            {
                Update<T>(conn, item, transaction);
            }
            isOk = true;
            return isOk;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids"></param>
        /// <returns></returns>
        public int DeleteBatch<T>(IDbConnection conn, IEnumerable<dynamic> ids, IDbTransaction transaction = null) where T : class
        {
            var tblName = GetTableName<T>();
            var idsin = string.Join(",", ids.ToArray<dynamic>());
            var sql = string.Format("DELETE FROM dbo.{0} WHERE ID in (@ids)", tblName);
            var result = SqlMapper.Execute(conn, sql, new { ids = idsin });

            return result;
        }
    }
}
